package org.springboot.szsrcb.srvice;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springboot.szsrcb.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

@Service
public class UserService {
	 private static final Logger logger = LoggerFactory.getLogger(UserService.class);
	 
	@Autowired
	private JdbcTemplate jdbcTemplate;
	/*
	public List<User> getUser(){
		String sql="SELECT ID,NAME FROM USER";
		return (List<User>)jdbcTemplate.query(sql, new RowMapper<User>() {
			@Override
			public User mapRow(ResultSet rs,int rowNum)throws SQLException{
				User u=new User();
				u.setId(rs.getInt("ID"));
				u.setName(rs.getString("NAME"));
				return u;	
			}
		});
				
	}*/

	public List<User> getList() {
		 String sql = "SELECT ID,NAME FROM USER";
		 logger.info("从数据库执行"+sql);
		 List<User> l_u= jdbcTemplate.query(sql, new UserRowMapper());
		 
			          /*  @Override
			            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
			            	User u = new User();
			                u.setId(rs.getInt("ID"));	               
			                u.setName(rs.getString("NAME"));
			                return u;
			            }

			        });*/
	        return (l_u);
	}
	

	

	class UserRowMapper implements RowMapper<User>{
		 @Override
         public User mapRow(ResultSet rs, int rowNum) throws SQLException {
         	User u = new User();
             u.setId(rs.getInt("ID"));	               
             u.setLogin_name(rs.getString("LOGIN_NAME"));
             u.setPwd(rs.getString("PWD"));
             u.setRole(rs.getString("role"));
             u.setName(rs.getString("NAME"));
             u.setRtx_account(rs.getString("rtx_account"));
             return u;
         }

	}

	public boolean checkLoginName(String loginName) {
		boolean result;
		 String sql = "SELECT * FROM USER where LOGIN_NAME='"+loginName+"'";
		 logger.info("从数据库执行"+sql);
		 List<User> l_u= jdbcTemplate.query(sql, new UserRowMapper());		 
			        
		 if(l_u.size()==1){
			 result=true;
		 }else{
			 result=false;
		 }			         
	    return result;
	}

	public boolean checkUserPWD(String loginName, String pwd) {
		boolean result;
		 String sql = "SELECT * FROM USER where LOGIN_NAME='"+loginName+"' and PWD='"+pwd+"'";
		 logger.info("从数据库执行"+sql);
		 List<User> l_u= jdbcTemplate.query(sql, new UserRowMapper());
		 if(l_u.size()==1){
			 result=true;
		 }else{
			 result=false;
		 }			         
	    return result;
	}

	public List<User> getAllUserList() {
		 String sql = "SELECT * FROM USER where role not in ('admin') order by convert(NAME USING gbk)";
		 logger.info("从数据库执行"+sql);
		 List<User> l_u= jdbcTemplate.query(sql, new UserRowMapper());
		return l_u;
	}
	
	public String trans_all_user_2_json() {
		 String sql = "SELECT * FROM USER where role not in ('admin') order by convert(NAME USING gbk)";
		 logger.info("从数据库执行"+sql);
		 List<User> l_u= jdbcTemplate.query(sql, new UserRowMapper());
		String json_s="";
		for (int i = 0; i < l_u.size(); i++) {
			User u = l_u.get(i);
			String tmp=u.getName();
			if(i==l_u.size()){
				json_s=json_s+"{value : '"+tmp+"',text:'"+tmp+"'}";
			}else{
				json_s=json_s+"{value : '"+tmp+"',text:'"+tmp+"'},";
			}
		}		
		
		return json_s;
	}

	public User search_a_user(String login_name) {
		String sql = "SELECT * FROM USER WHERE LOGIN_NAME='"+login_name+"' ";
		 List<User> l_u= jdbcTemplate.query(sql, new UserRowMapper());
		 if (l_u.size()>0) {
			return l_u.get(0);
		} else {
			return null;
		}
		
	}

	public User search_a_user_by_name(String name) {
		String sql = "SELECT * FROM USER WHERE name='"+name+"' ";
		 List<User> l_u= jdbcTemplate.query(sql, new UserRowMapper());
		 if (l_u.size()>0) {
			return l_u.get(0);
		} else {
			return null;
		}
		
	}
	
	public boolean is_admin(String user_name){
		boolean r=false;
		String sql = "SELECT * FROM USER WHERE role='admin' ";
		 List<User> l_u= jdbcTemplate.query(sql, new UserRowMapper());
		 for (int i = 0; i < l_u.size(); i++) {
			 User u = l_u.get(i);
			 String u_name=u.getName();
				if(u_name.equals(user_name)){
					r=true;
				}
		}
		 return r;	 
	}
	
	public boolean is_req_input(String user_name){
		boolean r=false;
		String sql = "SELECT * FROM USER WHERE role='需求录入用户' ";
		 List<User> l_u= jdbcTemplate.query(sql, new UserRowMapper());
		 for (int i = 0; i < l_u.size(); i++) {
			 User u = l_u.get(i);
			 String u_name=u.getName();
				if(u_name.equals(user_name)){
					r=true;
				}
		}
		 return r;	 
	}
	public boolean is_leader(String user_name){
		boolean r=false;
		String sql = "SELECT * FROM USER WHERE role='部门主管领导' ";
		 List<User> l_u= jdbcTemplate.query(sql, new UserRowMapper());
		 for (int i = 0; i < l_u.size(); i++) {
			 User u = l_u.get(i);
			 String u_name=u.getName();
				if(u_name.equals(user_name)){
					r=true;
				}
		}
		  sql = "SELECT * FROM USER WHERE role='项目管理中心负责人' ";
		 l_u= jdbcTemplate.query(sql, new UserRowMapper());
		 for (int i = 0; i < l_u.size(); i++) {
			 User u = l_u.get(i);
			 String u_name=u.getName();
				if(u_name.equals(user_name)){
					r=true;
				}
		}
		  sql = "SELECT * FROM USER WHERE role='业务方案中心负责人' ";
		  l_u= jdbcTemplate.query(sql, new UserRowMapper());
		 for (int i = 0; i < l_u.size(); i++) {
			 User u = l_u.get(i);
			 String u_name=u.getName();
				if(u_name.equals(user_name)){
					r=true;
				}
		}
		 
		 if(is_admin(user_name)){
				r=true;
		 };
		 return r;	 
	}

	public String find_deptment_manager() {
		String sql = "SELECT * FROM USER WHERE role='部门主管领导' ";
		 List<User> l_u= jdbcTemplate.query(sql, new UserRowMapper());
		 if (l_u.size()>0) {
			return l_u.get(0).getName();
		} else {
			return null;
		}
	}
	
	public String find_project_manager() {
		String sql = "SELECT * FROM USER WHERE role='项目管理中心负责人' AND status='可用' ";
		 List<User> l_u= jdbcTemplate.query(sql, new UserRowMapper());
		 if (l_u.size()>0) {
			return l_u.get(0).getName();
		} else {
			return null;
		}
	}

	public String find_business_manager() {
		String sql = "SELECT * FROM USER WHERE role='业务方案中心负责人' AND status='可用' ";
		 List<User> l_u= jdbcTemplate.query(sql, new UserRowMapper());
		 if (l_u.size()>0) {
			return l_u.get(0).getName();
		} else {
			return null;
		}
	}

	public boolean regrister_user(User user) {
		String sql = "INSERT INTO user 	(NAME,PWD,LOGIN_NAME,role,creat_time) VALUES ('"+ user.getName()+"','"+ user.getPwd()+"','"+ user.getLogin_name()+"','"+ user.getRole()+"',NOW());";
		logger.info("从数据库执行" + sql);
		int r= jdbcTemplate.update(sql);
		if (r>0) {
			return true;
		} else {
			return false;
		}
		
	}

	public boolean checkUserName(String Name) {
		boolean result;
		 String sql = "SELECT ID,NAME,PWD,LOGIN_NAME,role FROM USER where NAME='"+Name+"'";
		 logger.info("从数据库执行"+sql);
		 List<User> l_u= jdbcTemplate.query(sql, new UserRowMapper());		 
			        
		 if(l_u.size()>=1){
			 result=true;
		 }else{
			 result=false;
		 }			         
	    return result;
	}

	public boolean change_pwd(String login_name, String new_pwd) {
		
		boolean result=false;
		 String sql = "UPDATE USER SET pwd='"+new_pwd+"' WHERE login_name ='"+login_name+"'";
		 logger.info("从数据库执行"+sql);
		int r= jdbcTemplate.update(sql);		 
			        
		 if(r>=1){
			 result=true;
		 }else{
			 result=false;
		 }
		return result;			 
		
	}
	
public boolean change_rtx_account(String login_name, String rtx_account) {
		
		boolean result=false;
		 String sql = "UPDATE USER SET rtx_account='"+rtx_account+"' WHERE login_name ='"+login_name+"'";
		 logger.info("从数据库执行"+sql);
		int r= jdbcTemplate.update(sql);		 
			        
		 if(r>=1){
			 result=true;
		 }else{
			 result=false;
		 }
		return result;			 
		
	}

}
